package com.example.core;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.*;
import java.util.stream.Collectors;

/**
 * 表结构对比工具
 * 用于对比实体类定义和数据库实际表结构的差异
 */
@Slf4j
@Component
public class TableComparator {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    /**
     * 对比表结构差异
     */
    public TableDiff compareTable(String tableName, TableInfo expectedTable) {
        TableDiff diff = new TableDiff();
        diff.setTableName(tableName);
        
        // 获取数据库中的实际表结构
        Map<String, ColumnMetadata> actualColumns = getActualColumns(tableName);
        
        // 期望的列（从实体类解析）
        Map<String, TableInfo.ColumnInfo> expectedColumns = expectedTable.getColumns().stream()
                .collect(Collectors.toMap(TableInfo.ColumnInfo::getColumnName, c -> c));
        
        // 找出需要新增的列
        for (Map.Entry<String, TableInfo.ColumnInfo> entry : expectedColumns.entrySet()) {
            String columnName = entry.getKey();
            if (!actualColumns.containsKey(columnName.toLowerCase())) {
                diff.getColumnsToAdd().add(entry.getValue());
            }
        }
        
        // 找出需要删除的列
        for (String actualColumnName : actualColumns.keySet()) {
            if (!expectedColumns.containsKey(actualColumnName)) {
                diff.getColumnsToDrop().add(actualColumnName);
            }
        }
        
        // 找出需要修改的列（类型、长度等变化）
        for (Map.Entry<String, TableInfo.ColumnInfo> entry : expectedColumns.entrySet()) {
            String columnName = entry.getKey();
            TableInfo.ColumnInfo expectedColumn = entry.getValue();
            ColumnMetadata actualColumn = actualColumns.get(columnName.toLowerCase());
            
            if (actualColumn != null && isColumnModified(expectedColumn, actualColumn)) {
                diff.getColumnsToModify().add(expectedColumn);
            }
        }
        
        return diff;
    }
    
    /**
     * 获取数据库中实际的列信息
     */
    private Map<String, ColumnMetadata> getActualColumns(String tableName) {
        Map<String, ColumnMetadata> columns = new HashMap<>();
        
        try {
            String sql = "SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, " +
                        "IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT, COLUMN_KEY, EXTRA " +
                        "FROM INFORMATION_SCHEMA.COLUMNS " +
                        "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?";
            
            jdbcTemplate.query(sql, rs -> {
                ColumnMetadata metadata = new ColumnMetadata();
                metadata.setColumnName(rs.getString("COLUMN_NAME"));
                metadata.setDataType(rs.getString("DATA_TYPE"));
                metadata.setMaxLength(rs.getInt("CHARACTER_MAXIMUM_LENGTH"));
                metadata.setNullable("YES".equalsIgnoreCase(rs.getString("IS_NULLABLE")));
                metadata.setDefaultValue(rs.getString("COLUMN_DEFAULT"));
                metadata.setComment(rs.getString("COLUMN_COMMENT"));
                String columnKey = rs.getString("COLUMN_KEY");
                metadata.setUnique("UNI".equalsIgnoreCase(columnKey));
                metadata.setPrimaryKey("PRI".equalsIgnoreCase(columnKey));
                String extra = rs.getString("EXTRA");
                boolean hasAutoIncrement = extra != null && extra.toLowerCase().contains("auto_increment");
                metadata.setAutoIncrement(hasAutoIncrement);
                
                // 调试日志：输出所有字段的 EXTRA 信息（临时调试）
                log.info("字段 {} - COLUMN_KEY: '{}', EXTRA: '{}', AUTO_INCREMENT: {}", 
                         metadata.getColumnName(), columnKey, extra, hasAutoIncrement);
                
                columns.put(metadata.getColumnName().toLowerCase(), metadata);
            }, tableName);
            
        } catch (Exception e) {
            log.warn("获取表 {} 的列信息失败: {}", tableName, e.getMessage());
        }
        
        return columns;
    }
    
    /**
     * 判断列是否被修改
     */
    private boolean isColumnModified(TableInfo.ColumnInfo expected, ColumnMetadata actual) {
        // 简化判断：这里可以根据需要扩展更详细的对比逻辑
        // 比如对比类型、长度、是否允许为空等
        
        // 对比数据类型
        String expectedType = normalizeType(expected.getColumnType());
        String actualType = normalizeType(actual.getDataType());
        
        if (!expectedType.equalsIgnoreCase(actualType)) {
            log.debug("字段 {} 类型变化: {} -> {}", expected.getColumnName(), actualType, expectedType);
            return true;
        }
        
        // 对比长度（仅对VARCHAR等类型）
        if (expectedType.equalsIgnoreCase("VARCHAR") || expectedType.equalsIgnoreCase("CHAR")) {
            if (!expected.getLength().equals(actual.getMaxLength())) {
                log.debug("字段 {} 长度变化: {} -> {}", expected.getColumnName(), actual.getMaxLength(), expected.getLength());
                return true;
            }
        }
        
        // 对比 nullable 属性
        if (!expected.getNullable().equals(actual.getNullable())) {
            log.debug("字段 {} nullable 变化: {} -> {}", expected.getColumnName(), actual.getNullable(), expected.getNullable());
            return true;
        }
        
        // 对比 unique 约束
        if (!expected.getUnique().equals(actual.getUnique())) {
            log.debug("字段 {} unique 约束变化: {} -> {}", expected.getColumnName(), actual.getUnique(), expected.getUnique());
            return true;
        }
        
        // 对比 auto_increment 属性（特别重要，用于修复主键字段）
        if (expected.getAutoIncrement() != null && expected.getAutoIncrement()) {
            log.info("检查字段 {} 的 AUTO_INCREMENT - 期望: {}, 实际: {}, 实际是否为null: {}", 
                    expected.getColumnName(), 
                    expected.getAutoIncrement(), 
                    actual.getAutoIncrement(),
                    actual.getAutoIncrement() == null);
            
            if (actual.getAutoIncrement() == null || !actual.getAutoIncrement()) {
                log.warn("字段 {} 缺少 AUTO_INCREMENT 属性，需要修复 (期望: true, 实际: {})", 
                        expected.getColumnName(), actual.getAutoIncrement());
                return true;
            } else {
                log.info("字段 {} 的 AUTO_INCREMENT 属性正常，无需修改", expected.getColumnName());
            }
        }
        
        return false;
    }
    
    /**
     * 规范化类型名称
     */
    private String normalizeType(String type) {
        if (type == null) return "";
        
        // 移除括号及其内容
        int idx = type.indexOf('(');
        if (idx > 0) {
            type = type.substring(0, idx);
        }
        
        return type.trim().toUpperCase();
    }
    
    /**
     * 表结构差异
     */
    @Data
    public static class TableDiff {
        private String tableName;
        private List<TableInfo.ColumnInfo> columnsToAdd = new ArrayList<>();
        private List<String> columnsToDrop = new ArrayList<>();
        private List<TableInfo.ColumnInfo> columnsToModify = new ArrayList<>();
        
        public boolean hasChanges() {
            return !columnsToAdd.isEmpty() || !columnsToDrop.isEmpty() || !columnsToModify.isEmpty();
        }
    }
    
    /**
     * 数据库列元数据
     */
    @Data
    private static class ColumnMetadata {
        private String columnName;
        private String dataType;
        private Integer maxLength;
        private Boolean nullable;
        private String defaultValue;
        private String comment;
        private Boolean unique = false;
        private Boolean primaryKey = false;
        private Boolean autoIncrement = false;
    }
}
